/**
 * 
 */
package net.sf.cldsql.dj.db;

import java.lang.reflect.Array;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.io.IOUtils;
import org.dom4j.Branch;
import org.dom4j.CDATA;
import org.dom4j.CharacterData;
import org.dom4j.Comment;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.DocumentType;
import org.dom4j.Element;
import org.dom4j.Entity;
import org.dom4j.Node;
import org.dom4j.Text;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;

import net.sf.cldsql.dj.container.DiContainer;
import net.sf.cldsql.dj.utl.DiException;
import net.sf.cldsql.dj.utl.StrUtl;
import net.sf.cldsql.sql2json.main.SjConf;

/**
 * QueryProxy
 */
public class QueryProxy<T> implements InvocationHandler {
    
    Class<T> queryClass;
    DbConnection conn;
    Logger log;
    DiContainer container;
    
    static final private StrUtl strUtl = new StrUtl();
    
    public void init() {
        if (container.get(DiSqlCache.class) == null) {
            synchronized (container) {
                if (container.get(DiSqlCache.class) == null) {
                    container.register(DiSqlCache.class, DiContainer.SINGLETON);
                }
            }
        }
    }
    
    public Object paramMapGet(Map<String, Object> paramMap, String propName) {
        Object ret;
        int pos;
        if ((pos = propName.indexOf('.')) > 0) {
            
            Object paramObj = paramMap.get(propName.substring(0, pos));
            try {
                ret = BeanUtils.getProperty(paramObj, propName.substring(pos + 1));
            } catch (IllegalAccessException e) {
                throw new DiException(e);
            } catch (InvocationTargetException e) {
                throw new DiException(e);
            } catch (NoSuchMethodException e) {
                throw new DiException(e);
            }
        } else {
            ret = paramMap.get(propName);
        }
        return ret;
    }

    /**
     * @return container
     */
    public DiContainer getContainer() {
        return container;
    }
    public QueryProxy (Class<T> queryClass) {
        this.queryClass = queryClass;
    }
    @SuppressWarnings("unchecked")
    public T newInstance() {
        return (T)Proxy.newProxyInstance(
                queryClass.getClassLoader(), 
                new Class[] {queryClass}, 
                this);
    }

    @SuppressWarnings("unchecked")
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        if (method.getName().equals("getProxy")) { // for test
            return this;
        } else if (method.getName().equals("setConn")) {
            this.conn = (DbConnection)args[0];
            return null;
        } else if (method.getName().equals("setLog")) {
            this.log = (Logger)args[0];
            return null;
        } else if (method.getName().equals("setContainer")) {
            this.container = (DiContainer)args[0];
            return null;
        } else if (method.getName().equals("toString")) {
            return queryClass.getName();
        } else if (method.getName().equals("init")) {
            init();
            return null;
        } else if (method.getName().startsWith("exec")) {
            
            return (Object)execQuery(method, (Map<String, Object>)args[0]);
            
        } else {
            if (log != null) log.debug("Unknown method {}", method);
            return null;
        }
    }
    /** @param paramMap 
     * @return */
    QueryResult execQuery(Method method, Map<String, Object> paramMap) throws Throwable {
        long beginTs = System.currentTimeMillis();
        
        String filename = queryClass.getSimpleName() + "." + method.getName() + ".sql.xml";
        if (queryClass.getResource(filename) == null) {
            filename = queryClass.getSimpleName() + "." + method.getName() + "." + conn.getPool().getDbms() + ".sql.xml";
        }
        /*
        String content = loadFile(filename);
        String sql = extractSql(content);
        SqlBind sqlBind = buildSqlBind(sql, paramMap);
        */
        
        SqlBind sqlBind = loadSql(filename, paramMap);
        
        QueryResult result = execAndFetchResult(sqlBind);
        
        log.info("executed cost {} ms | sql {} | param {} | result {}", System.currentTimeMillis() - beginTs, strUtl.flattenCrLf(sqlBind.getSql()), sqlBind.getParameters(), result);
        
        return result;
    }
    /**
     * @param filename
     * @param paramMap
     * @return
     * @throws Throwable 
     */
    @SuppressWarnings("unchecked")
    private SqlBind loadSql(String filename, Map<String, Object> paramMap) throws Throwable {
        Element root = loadDoc(filename);
        return buildSqlBind(root, paramMap);
    }
    /** @param filename
    /** @return */
    private Element loadDoc(String filename) throws Throwable {
        URL fileUrl = queryClass.getResource(filename);
        //log.info("load sql xml from {} and convert with {} to {}", filename, queryClass, fileUrl);
        
        //URL fileUrl = queryClass.getResource(".");
        //log.info("load sql xml from {} and convert with {} to dir {}", filename, queryClass, fileUrl);
        //fileUrl= new URL(fileUrl.toString() + filename);
        log.info("load sql xml from {} and convert with {} to {}", filename, queryClass, fileUrl);
        DiSqlCache cache = container.get(DiSqlCache.class);
        Document doc;
        ParsedSql parsedSql;
        if ((parsedSql = cache.get(fileUrl.toString())) == null) {
            SAXReader reader = new SAXReader();
            doc = reader.read(fileUrl);//queryClass.getResourceAsStream(filename)
            cache.put(fileUrl.toString(), new ParsedSql(doc));
            log.info("load sql xml from {} and found {}", filename, doc);
        } else {
            doc = parsedSql.getDocument();
        }
        Element root = doc.getRootElement();
        if (!root.getName().equals("sql")) {
            throw new DiException("Invalid xml doc " + doc);
        }
        return root;
    }
    
    private Element loadSqlXmlStr (String xml) throws Throwable {
        DiSqlCache cache = container.get(DiSqlCache.class);
        Document doc;
        ParsedSql parsedSql;
        if ((parsedSql = cache.get(xml)) == null) {
            doc = DocumentHelper.parseText(xml);
            cache.put(xml, new ParsedSql(doc));
        } else {
            doc = parsedSql.getDocument();
        }
        Element root = doc.getRootElement();
        if (!root.getName().equals("sql")) {
            throw new DiException("Invalid xml doc " + doc);
        }
        return root;
    }

    /**
     * @param filename
     * @param paramMap
     * @return
     * @throws Throwable 
     */
    @SuppressWarnings("unchecked")
    SqlBind parseSql(String xml, Map<String, Object> paramMap) throws Throwable {
        Element elem = loadSqlXmlStr(xml);
        return buildSqlBind(elem, paramMap);
    }
    /** @param elem
    /** @param paramMap
    /** @return 
     * @throws Throwable */
    @SuppressWarnings("unchecked")
    private SqlBind buildSqlBind(Branch parent, Map<String, Object> paramMap) throws Throwable {
        StringBuilder sql = new StringBuilder();
        ArrayList<Object> paramList = new ArrayList<Object>();
        
        Node node;
        for (Iterator<Node> itr = parent.nodeIterator(); itr.hasNext() && (node = itr.next()) != null;) {
            if (node instanceof Element) {
                Element elem = (Element)node;
                String tagName = node.getName();
                if ("if".equals(tagName)) {
                    String prm = elem.attributeValue("prm") != null ? elem.attributeValue("prm") : elem.attributeValue("prop") != null ? elem.attributeValue("prop") : elem.attributeValue("property");
                    String opr = elem.attributeValue("opr") != null ? elem.attributeValue("opr") : elem.attributeValue("operator");
                    String val = elem.attributeValue("val") != null ? elem.attributeValue("val") : elem.attributeValue("value");
                    if (compute (paramMapGet(paramMap, prm), opr, val)) {
                        SqlBind innerSqlBind = buildSqlBind(elem, paramMap);
                        sql.append(innerSqlBind.getSql());
                        paramList.addAll(innerSqlBind.getParameters());
                    }
                } else if ("foreach".equals(tagName)) {
                    String itemName = elem.attributeValue("item");
                    String indexName = elem.attributeValue("index");
                    String collName = elem.attributeValue("coll") != null ? elem.attributeValue("coll") : elem.attributeValue("collection");
                    String openStr = elem.attributeValue("open");
                    String seperatorStr = elem.attributeValue("sepr") != null ? elem.attributeValue("sepr") : elem.attributeValue("seperator");
                    String closeStr = elem.attributeValue("close");
                    
                    Object collObj = paramMapGet(paramMap, collName);
                    Collection coll;
                    if (collObj.getClass().isArray()) {
                        if (collObj instanceof Object) {
                        coll = Arrays.asList((Object[])collObj);
                        } else if (collObj instanceof int[]) {
                            coll = Arrays.asList((int[])collObj);
                        } else if (collObj instanceof short[]) {
                            coll = Arrays.asList((short[])collObj);
                        } else if (collObj instanceof long[]) {
                            coll = Arrays.asList((long[])collObj);
                        } else if (collObj instanceof float[]) {
                            coll = Arrays.asList((float[])collObj);
                        } else if (collObj instanceof double[]) {
                            coll = Arrays.asList((double[])collObj);
                        } else if (collObj instanceof char[]) {
                            coll = Arrays.asList((char[])collObj);
                        } else if (collObj instanceof boolean[]) {
                            coll = Arrays.asList((boolean[])collObj);
                        } else {
                            throw new DiException("Unkown array type " + collObj);
                        }
                    } else {
                        coll = (Collection)collObj;
                    }
                    sql.append(openStr);
                    int index = 0;
                    for (Object item : coll) {
                        if (index != 0) {
                            sql.append(seperatorStr);
                        }
                        paramMap.put(itemName, item);
                        paramMap.put(indexName, index);

                        SqlBind innerSqlBind = buildSqlBind(elem, paramMap);
                        sql.append(innerSqlBind.getSql());
                        paramList.addAll(innerSqlBind.getParameters());
                        
                        index++;
                    }
                    sql.append(closeStr);
                } else if ("source".equals(tagName)) {
                    String prm = elem.attributeValue("prm") != null ? elem.attributeValue("prm") : elem.attributeValue("property");
                    
                    Document doc = DocumentHelper.parseText((String)paramMapGet(paramMap, prm));
                    Element root = doc.getRootElement();
                    if (root.getName().equals("sql")) {
                        SqlBind innerSqlBind = buildSqlBind(root, paramMap);
                        sql.append(innerSqlBind.getSql());
                        paramList.addAll(innerSqlBind.getParameters());
                    } else {
                        throw new DiException("Invalid xml doc " + doc);
                    }
                } else {
                    new DiException("Unknown XML element " + node);
                }
            } else if (node instanceof Branch) {
            } else if (node instanceof CharacterData) {
                SqlBind sqlBind = buildSqlBind(node.getText(), paramMap);
                sql.append(sqlBind.getSql());
                paramList.addAll(sqlBind.getParameters());
            } else if (node instanceof CDATA) {
            } else if (node instanceof Text) {
            } else if (node instanceof Comment) {
            } else if (node instanceof Document) {
            } else if (node instanceof DocumentType) {
            } else if (node instanceof Entity) {
            }
        }
        
        return new SqlBind(sql.toString(), paramList);
    }
    /**
     * @param content
     * @return
     */
    String extractSql(String content) throws Exception{
        
        String sqlKey = "sql:|\n";
        String ret = null;
        
        int pos;
        if ((pos = content.indexOf(sqlKey)) >= 0) {
            ret = content.substring(pos + sqlKey.length());
        }
        sqlKey = "sql:|\r\n";
        if ((pos = content.indexOf(sqlKey)) >= 0) {
            ret = content.substring(pos + sqlKey.length());
        }
        if (ret == null) {
            new DiException("Cannot load sql from " + content);
        }
        return ret;
    }
    /** @param stmt
    /** @return */
    QueryResult execAndFetchResult(SqlBind sqlBind) throws Throwable {
        QueryResult result = null;
        PreparedStatement stmt = conn.getConn().prepareStatement(sqlBind.getSql());
        try {
            for (int i = 0; i < sqlBind.getParameters().size(); i ++) {
                Object p = sqlBind.getParameters().get(i);
                if (p == null) {
                    stmt.setString(i + 1, (String)p);
                } else if (p instanceof String) {
                    stmt.setString(i + 1, (String)p);
                } else if (p instanceof Integer) {
                    stmt.setInt(i + 1, (Integer)p);
                } else if (p instanceof Long) {
                    stmt.setLong(i + 1, (Long)p);
                } else if (p instanceof Boolean) {
                    stmt.setBoolean(i + 1, (Boolean)p);
                } else if (p instanceof Float) {
                    stmt.setFloat(i + 1, (Float)p);
                } else if (p instanceof Double) {
                    stmt.setDouble(i + 1, (Double)p);
                } else if (p instanceof BigDecimal) {
                    stmt.setBigDecimal(i + 1, (BigDecimal)p);
                } else {
                    throw new DiException ("Unknow parameter type " + p.getClass().getCanonicalName());
                }
            }
    
            long execTs = System.currentTimeMillis();
            boolean hasResult = stmt.execute();
            log.debug("sql execute time {} ms .", System.currentTimeMillis() - execTs);
            
            long fetchTs = System.currentTimeMillis();
            if (hasResult) {
                ArrayList<Map<String, Object>> resultMap = new ArrayList<Map<String, Object>>();
                ResultSet rset = stmt.getResultSet();
                try {
                    for (int rownum = 0; rset.next(); rownum ++) {
                        Map<String, Object> colMap = new HashMap<String, Object>();
                        for (int i = 0; i < rset.getMetaData().getColumnCount(); i ++) {
                            String columnName;
                            if (SjConf.JDBC_RSET_NAMING_STYLE_JAVA.equals(conn.getPool().getRsetNamingStyle())) {
                                columnName = StrUtl.get().convSqlToLJava(rset.getMetaData().getColumnName(i + 1));
                            } else {
                                columnName = rset.getMetaData().getColumnName(i + 1);
                            }
                            colMap.put(columnName, rset.getString(i + 1));
                        }
                        resultMap.add(colMap);
                    }
                } finally {
                    rset.close();
                }
                result = new QueryResult(resultMap);
            } else {
                result = new QueryResult(null, stmt.getUpdateCount());
            }
            log.debug("sql fetch time {} ms .", System.currentTimeMillis() - fetchTs);
        } finally {
            stmt.close();
        }
        
        return result;
    }
    
    StringBuilder buildSql(String sqlSrc, Map<String, Object> paramMap) throws Throwable {
        StringBuilder sql = new StringBuilder();
        
        {
            //#<text prm='tableName'/>#
            Matcher matcher = Pattern.compile("<(text) +(prm)='([^']+)' */>").matcher(sqlSrc);
            int curPos = 0;
            for (; matcher.find(); curPos = matcher.end()) {
                if (matcher.group(1).equals("text") && matcher.group(2).equals("prm")) {
                    sql.append(sqlSrc.substring(curPos, matcher.start()));
                    sql.append(paramMapGet(paramMap, matcher.group(3)));
                } else {
                    throw new DiException ("Error in parse " + matcher.group(0) + ".");
                }
            }
            sql.append(sqlSrc.substring(curPos));
            sqlSrc = sql.toString();
            sql.delete(0, sql.length());
        }

        {
            // #<if prm='id' opr='>' val='0'># ... #</if>#
            //^([^#]*)(#<(if) +(prm)='([^']+)' +(opr)='([^']+)' +(val)='([^']*)' *>#(.*)#</(\\3)>#)([^#]*)$
            Matcher matcher = Pattern.compile("^([^<]*)(<(if) +(prm)='([^']+)' +(opr)='([^']+)' +(val)='([^']*)'[^>]*>(.*)</(\\3)>)([^>]*)$").matcher(sqlSrc);
            int curPos = 0;
            for (; matcher.find(); curPos = matcher.end(2)) {
                if (matcher.group(3).equals("if") 
                        && matcher.group(4).equals("prm")
                        && matcher.group(6).equals("opr")
                        && matcher.group(8).equals("val")) {
                    sql.append(sqlSrc.substring(curPos, matcher.start(2)));
                    String prm = matcher.group(5);
                    String opr = matcher.group(7);
                    String val = matcher.group(9);
                    String innerSqlSrc = matcher.group(10);
                    if (compute (paramMapGet(paramMap, prm), opr, val)) {
                        StringBuilder innerSql = buildSql(innerSqlSrc, paramMap);
                        sql.append(innerSql);
                    }
                } else {
                    throw new DiException ("Error in parse " + matcher.group(0) + ".");
                }
            }
            sql.append(sqlSrc.substring(curPos));
            sqlSrc = sql.toString();
            sql.delete(0, sql.length());
        }

        return sql.append(sqlSrc);
    }
    
    /** @param sql
    /** @param paramMap
    /** @return */
    SqlBind buildSqlBind(String sqlSrc, Map<String, Object> paramMap) throws Throwable {
        ArrayList<Object> paramList = new ArrayList<Object>();
        StringBuilder sql = new StringBuilder();
        
        {
            Matcher matcher = Pattern.compile("#([^#]*)#").matcher(sqlSrc);
            int curPos = 0;
            for (; matcher.find(); curPos = matcher.end()) {
                sql.append(sqlSrc.substring(curPos, matcher.start()));
                sql.append("?");
                paramList.add(paramMapGet(paramMap, matcher.group(1)));
            }
            sql.append(sqlSrc.substring(curPos));
            sqlSrc = sql.toString();
            sql.delete(0, sql.length());
        }
        
        log.debug("parsed sql {} paramList {} .", strUtl.flattenCrLf(sqlSrc), paramList);
        SqlBind sqlBind = new SqlBind(sqlSrc, paramList);
        return sqlBind;
    }
    
    /**
     * @param object
     * @param opr
     * @param val
     * @return
     */
    @SuppressWarnings("unchecked")
    private boolean compute(Object object, String opr, String val) {
        boolean ret = false;
        if (opr.equals("is") || opr.equals("isNot")) {
            if (val.equals("null")) {
                ret = object == null;
            } else if (val.equals("empty")) {
                if (object == null) {
                    ret = true;
                } else if (object instanceof String) {
                    ret = ((String)object).isEmpty();
                } else if (object instanceof Collection) {
                    ret = ((Collection)object).isEmpty();
                } else if (object.getClass().isArray()) {
                    ret = Array.getLength(object) == 0;
                } else {
                    ret = false;
                }
            } else {
                throw new DiException("Unknown opr 'is' val '" + val + "'");
            }
            if (opr.equals("isNot")) {
                ret = !ret;
            }
        } else if (object != null) {
            if (!(object instanceof Comparable)) {
                throw new DiException("Cannot compare " + object + " to " + val + " with operator " + opr);
            } else {
                try {
                    int result = ((Comparable)object).compareTo(object.getClass().getConstructor(new Class[]{String.class}).newInstance(new Object[]{val}));
                    if (opr.equals("<")) {
                        ret = result < 0;
                    } else if (opr.equals("<=")) {
                        ret = result <= 0;
                    } else if (opr.equals(">")) {
                        ret = result > 0;
                    } else if (opr.equals(">=")) {
                        ret = result >= 0;
                    } else if (opr.equals("=") || opr.equals("==")) {
                        ret = result == 0;
                    } else if (opr.equals("!=") || opr.equals("<>")) {
                        ret = result != 0;
                    } else {
                        throw new DiException("Unknown operator " + opr);
                    }
                } catch (IllegalArgumentException e) {
                    throw new DiException(e);
                } catch (SecurityException e) {
                    throw new DiException(e);
                } catch (InstantiationException e) {
                    throw new DiException(e);
                } catch (IllegalAccessException e) {
                    throw new DiException(e);
                } catch (InvocationTargetException e) {
                    throw new DiException(e);
                } catch (NoSuchMethodException e) {
                    throw new DiException(e);
                }
            }
        }
        return ret;
    }
    /** @param filename
    /** @return */
    String loadFile(String filename) throws Throwable{
        log.debug("Load file {} ", filename);
        /*
        BufferedReader reader = new BufferedReader(new InputStreamReader(queryClass.getResourceAsStream(filename), "UTF-8"));
        StringBuilder buf = new StringBuilder();
        for (String line; (line = reader.readLine()) != null;) {
            buf.append(line).append('\n');
        }
        return buf.toString();
        */
        return IOUtils.toString(queryClass.getResourceAsStream(filename), "UTF-8");
    }

}
